<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0"><meta name="theme-color" content="#0078E7"><meta name="author" content="zzl"><meta name="copyright" content="zzl"><meta name="generator" content="Hexo 5.4.0"><meta name="theme" content="hexo-theme-yun"><title>sql语言 | zzl</title><link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@900&amp;display=swap" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/star-markdown-css@0.1.25/dist/yun/yun-markdown.min.css"><script src="//at.alicdn.com/t/font_1140697_dxory92pb0h.js" async></script><script src="https://cdn.jsdelivr.net/npm/scrollreveal/dist/scrollreveal.min.js" defer></script><script>document.addEventListener("DOMContentLoaded", () => {
  [".post-card",".post-content img"].forEach((target)=> {
    ScrollReveal().reveal(target);
  })
});
</script><link rel="icon" href="/web/yun.svg"><link rel="mask-icon" href="/web/yun.svg" color="#0078E7"><link rel="alternate icon" href="/web/yun.ico"><link rel="preload" href="/web/css/hexo-theme-yun.css" as="style"><link rel="preload" href="/web/js/utils.js" as="script"><link rel="preload" href="/web/js/hexo-theme-yun.js" as="script"><link rel="prefetch" href="/web/js/sidebar.js" as="script"><link rel="preconnect" href="https://cdn.jsdelivr.net" crossorigin><script id="yun-config">
    const Yun = window.Yun || {};
    window.CONFIG = {"hostname":"zilongzeng.gitee.io","root":"/web/","title":"奏者的小站","version":"1.6.2","mode":"auto","copycode":true,"page":{"isPost":true},"i18n":{"placeholder":"Searching...","empty":"We didn't find any results for the search: ${query}.","hits":"${hits} results found","hits_time":"${hits} results found in ${time} ms"},"anonymous_image":"https://cdn.jsdelivr.net/gh/YunYouJun/cdn/img/avatar/none.jpg","say":{"api":"https://v1.hitokoto.cn","hitokoto":true},"fireworks":{"colors":["102, 167, 221","62, 131, 225","33, 78, 194"]}};
  </script><link rel="stylesheet" href="/web/css/hexo-theme-yun.css"><script src="/web/js/utils.js"></script><script src="/web/js/hexo-theme-yun.js"></script><meta name="description" content="day1:数据库建模 一、建立系统的ER图（实体—联系） 1、找出系统中涉及的实体（完整的事物、人或物） 实体的属性。   2、找出实体之间的联系（两个实体之间或者多个实体之间） 1：1 1：N M:N联系产生的属性备注：联系不是系统实现后操作关系，而是实体之间逻辑对应关系    二、设计数据库表结构 以ER图作为基础，确定数据库里面包含的表以及每个表包含的字段   1、ER图上每一个实体对应一个">
<meta property="og:type" content="article">
<meta property="og:title" content="sql语言">
<meta property="og:url" content="http://zilongzeng.gitee.io/web/2022/04/28/sql%E8%AF%AD%E8%A8%80/index.html">
<meta property="og:site_name" content="zzl">
<meta property="og:description" content="day1:数据库建模 一、建立系统的ER图（实体—联系） 1、找出系统中涉及的实体（完整的事物、人或物） 实体的属性。   2、找出实体之间的联系（两个实体之间或者多个实体之间） 1：1 1：N M:N联系产生的属性备注：联系不是系统实现后操作关系，而是实体之间逻辑对应关系    二、设计数据库表结构 以ER图作为基础，确定数据库里面包含的表以及每个表包含的字段   1、ER图上每一个实体对应一个">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2022-04-28T06:46:29.000Z">
<meta property="article:modified_time" content="2022-04-28T06:55:14.990Z">
<meta property="article:author" content="zzl">
<meta property="article:tag" content="sql">
<meta name="twitter:card" content="summary"><script src="/web/js/ui/mode.js"></script></head><body><script defer src="https://cdn.jsdelivr.net/npm/animejs@latest"></script><script defer src="/web/js/ui/fireworks.js"></script><canvas class="fireworks"></canvas><div class="container"><a class="sidebar-toggle hty-icon-button" id="menu-btn"><div class="hamburger hamburger--spin" type="button"><span class="hamburger-box"><span class="hamburger-inner"></span></span></div></a><div class="sidebar-toggle sidebar-overlay"></div><aside class="sidebar"><script src="/web/js/sidebar.js"></script><ul class="sidebar-nav"><li class="sidebar-nav-item sidebar-nav-toc hty-icon-button sidebar-nav-active" data-target="post-toc-wrap" title="Table of Contents"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-list-ordered"></use></svg></li><li class="sidebar-nav-item sidebar-nav-overview hty-icon-button" data-target="site-overview-wrap" title="Overview"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-passport-line"></use></svg></li></ul><div class="sidebar-panel" id="site-overview-wrap"><div class="site-info fix-top"><a class="site-author-avatar" href="/web/about/" title="zzl"><img width="96" loading="lazy" src="/web/zzl.jpg" alt="zzl"><span class="site-author-status" title="永远相信美好的事情即将发生">😊</span></a><div class="site-author-name"><a href="/web/about/">zzl</a></div><span class="site-name">zzl</span><sub class="site-subtitle"></sub><div class="site-desciption"></div></div><nav class="site-state"><a class="site-state-item hty-icon-button icon-home" href="/web/" title="Home"><span class="site-state-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-home-4-line"></use></svg></span></a><div class="site-state-item"><a href="/web/archives/" title="Archives"><span class="site-state-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-archive-line"></use></svg></span><span class="site-state-item-count">16</span></a></div><div class="site-state-item"><a href="/web/categories/" title="Categories"><span class="site-state-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-folder-2-line"></use></svg></span><span class="site-state-item-count">0</span></a></div><div class="site-state-item"><a href="/web/tags/" title="Tags"><span class="site-state-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-price-tag-3-line"></use></svg></span><span class="site-state-item-count">9</span></a></div><a class="site-state-item hty-icon-button" target="_blank" rel="noopener" href="https://yun.yunyoujun.cn" title="文档"><span class="site-state-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-settings-line"></use></svg></span></a></nav><hr style="margin-bottom:0.5rem"><div class="links-of-author"><a class="links-of-author-item hty-icon-button" rel="noopener" href="/atom.xml" title="RSS" target="_blank" style="color:orange"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-rss-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="QQ 2915667803" target="_blank" style="color:#12B7F5"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-qq-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="GitHub" target="_blank" style="color:#6e5494"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-github-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="微博" target="_blank" style="color:#E6162D"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-weibo-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="豆瓣" target="_blank" style="color:#007722"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-douban-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="网易云音乐" target="_blank" style="color:#C20C0C"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-netease-cloud-music-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="知乎" target="_blank" style="color:#0084FF"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-zhihu-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="哔哩哔哩" target="_blank" style="color:#FF8EB3"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-bilibili-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="微信公众号" target="_blank" style="color:#1AAD19"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-wechat-2-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="Twitter" target="_blank" style="color:#1da1f2"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-twitter-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="Telegram Channel" target="_blank" style="color:#0088CC"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-telegram-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="E-Mail" target="_blank" style="color:#8E71C1"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-mail-line"></use></svg></a><a class="links-of-author-item hty-icon-button" rel="noopener" title="Travelling" target="_blank" style="color:var(--hty-text-color)"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-train-line"></use></svg></a></div><hr style="margin:0.5rem 1rem"><div class="links"><a class="links-item hty-icon-button" href="/web/links/" title="我的小伙伴们" style="color:dodgerblue"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-genderless-line"></use></svg></a></div><br><a class="links-item hty-icon-button" id="toggle-mode-btn" href="javascript:;" title="Mode" style="color: #f1cb64"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-contrast-2-line"></use></svg></a></div><div class="sidebar-panel sidebar-panel-active" id="post-toc-wrap"><div class="post-toc"><div class="post-toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#day1-%E6%95%B0%E6%8D%AE%E5%BA%93%E5%BB%BA%E6%A8%A1"><span class="toc-number">1.</span> <span class="toc-text">day1:数据库建模</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E5%BB%BA%E7%AB%8B%E7%B3%BB%E7%BB%9F%E7%9A%84ER%E5%9B%BE%EF%BC%88%E5%AE%9E%E4%BD%93%E2%80%94%E8%81%94%E7%B3%BB%EF%BC%89"><span class="toc-number">1.1.</span> <span class="toc-text">一、建立系统的ER图（实体—联系）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E8%AE%BE%E8%AE%A1%E6%95%B0%E6%8D%AE%E5%BA%93%E8%A1%A8%E7%BB%93%E6%9E%84"><span class="toc-number">1.2.</span> <span class="toc-text">二、设计数据库表结构</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day2-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%88%9B%E5%BB%BA"><span class="toc-number">2.</span> <span class="toc-text">day2:数据库的创建</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E8%A1%A8%E7%BB%93%E6%9E%84%E7%9A%84%E5%88%9B%E5%BB%BA"><span class="toc-number">2.1.</span> <span class="toc-text">一、表结构的创建</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E6%95%B0%E6%8D%AE%E7%9A%84%E5%BD%95%E5%85%A5"><span class="toc-number">2.2.</span> <span class="toc-text">二、数据的录入</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%89%E3%80%81sql%E7%9A%84%E4%BD%BF%E7%94%A8%EF%BC%88%E7%AE%80%E5%8D%95%E6%9F%A5%E8%AF%A2%E3%80%81%E5%A4%8D%E5%90%88%E6%9F%A5%E8%AF%A2%E3%80%81%E7%BB%9F%E8%AE%A1%EF%BC%89"><span class="toc-number">2.3.</span> <span class="toc-text">三、sql的使用（简单查询、复合查询、统计）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%9B%9B%E3%80%81%E8%A7%86%E5%9B%BEview%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">2.4.</span> <span class="toc-text">四、视图view的使用</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%94%E3%80%81%E8%A7%A6%E5%8F%91%E5%99%A8trigger%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">2.5.</span> <span class="toc-text">五、触发器trigger的使用</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%85%AD%E3%80%81%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">2.6.</span> <span class="toc-text">六、存储过程的使用</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day3-%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.</span> <span class="toc-text">day3:多表查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day3%EF%BC%9A%E7%BB%9F%E8%AE%A1"><span class="toc-number">4.</span> <span class="toc-text">day3：统计</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day4"><span class="toc-number">5.</span> <span class="toc-text">day4:</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1%E3%80%81limit%E5%92%8Corder-by-%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">5.1.</span> <span class="toc-text">1、limit和order by 的使用</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2%E3%80%81%E8%A7%86%E5%9B%BEview%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">5.2.</span> <span class="toc-text">2、视图view的使用</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day5"><span class="toc-number">6.</span> <span class="toc-text">day5</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%88%90%E7%BB%A9%E7%9B%B8%E5%85%B3%E7%9A%84%E8%A1%A8"><span class="toc-number">6.1.</span> <span class="toc-text">创建成绩相关的表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9E%E7%8E%B0%E5%AF%B9%E6%9F%90%E9%97%A8%E8%AF%BE%E7%A8%8B-%E6%9F%90%E4%B8%AA%E7%8F%AD%E7%BA%A7%E6%9C%9F%E6%9C%AB%E6%88%90%E7%BB%A9%E7%9A%84%E5%88%86%E6%9E%90"><span class="toc-number">6.2.</span> <span class="toc-text">实现对某门课程 某个班级期末成绩的分析</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day6"><span class="toc-number">7.</span> <span class="toc-text">day6</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#day7"><span class="toc-number">8.</span> <span class="toc-text">day7</span></a></li></ol></div></div></div></aside><main class="sidebar-translate" id="content"><div id="post"><article class="hty-card post-block" itemscope itemtype="https://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://zilongzeng.gitee.io/web/web/2022/04/28/sql%E8%AF%AD%E8%A8%80/"><span hidden itemprop="author" itemscope itemtype="https://schema.org/Person"><meta itemprop="name" content="zzl"><meta itemprop="description"></span><span hidden itemprop="publisher" itemscope itemtype="https://schema.org/Organization"><meta itemprop="name" content="zzl"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">sql语言</h1><div class="post-meta"><div class="post-time" style="display:inline-block"><span class="post-meta-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-calendar-line"></use></svg></span> <time title="Created: 2022-04-28 14:46:29" itemprop="dateCreated datePublished" datetime="2022-04-28T14:46:29+08:00">2022-04-28</time></div><div class="post-classify"><span class="post-tag"><a class="tag-item" href="/web/tags/sql/" style="--text-color:var(--hty-text-color)"><span class="post-meta-item-icon"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-price-tag-3-line"></use></svg></span><span class="tag-name">sql</span></a></span></div></div></header><section class="post-body" itemprop="articleBody"><div class="post-content markdown-body" style="--smc-primary:#0078E7;"><h2 id="day1-数据库建模"><a href="#day1-数据库建模" class="headerlink" title="day1:数据库建模"></a>day1:数据库建模</h2><hr>
<h3 id="一、建立系统的ER图（实体—联系）"><a href="#一、建立系统的ER图（实体—联系）" class="headerlink" title="一、建立系统的ER图（实体—联系）"></a>一、建立系统的<code>ER图</code>（实体—联系）</h3><ul>
<li>1、找出系统中涉及的<code>实体</code>（完整的事物、人或物）<blockquote>
<p>实体的属性。</p>
</blockquote>
</li>
<li>2、找出实体之间的<code>联系</code>（两个实体之间或者多个实体之间）<blockquote>
<p>1：1 1：N M:N<br>联系产生的属性<br>备注：联系不是系统实现后操作关系，而是实体之间逻辑对应关系</p>
</blockquote>
</li>
</ul>
<h3 id="二、设计数据库表结构"><a href="#二、设计数据库表结构" class="headerlink" title="二、设计数据库表结构"></a>二、设计数据库<code>表结构</code></h3><blockquote>
<p>以ER图作为基础，确定数据库里面包含的表以及每个表包含的字段</p>
</blockquote>
<ul>
<li><p>1、ER图上每一个实体对应一个表，每个实体的属性对应一个字段，额外增加一个id字段（自增）作为主键（唯一）</p>
</li>
<li><p>2、ER图上的联系(<code>表数量要与实体对应</code>)    </p>
</li>
</ul>
<blockquote>
<p>（1）1:1的联系：可以任意在某个实体对应的表里面，增加一个外键字段，指向另外一个实体<br>（2）1：N的联系：只能在N这个实体表里面，增加一个外键字段，指向1端实体<br>（3）N:M（多个实体）的联系：<code>单独增加一个表</code>，包含指向每个实体的外键字段，加上联系对应属性作为字段，额外增加一个id字段（自增）作为主键（唯一）</p>
</blockquote>
<h2 id="day2-数据库的创建"><a href="#day2-数据库的创建" class="headerlink" title="day2:数据库的创建"></a>day2:数据库的创建</h2><hr>
<h3 id="一、表结构的创建"><a href="#一、表结构的创建" class="headerlink" title="一、表结构的创建"></a>一、表结构的创建</h3><ul>
<li>1、学院信息表    <ul>
<li>新建数据库 school</li>
<li>新建表<blockquote>
<p>college、major、teacher、classes、student</p>
</blockquote>
</li>
<li>数据的查询SQL<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1</span>、简单单表查询</span><br><span class="line"> <span class="keyword">select</span> 字段名</span><br><span class="line"> <span class="keyword">from</span> 表名</span><br><span class="line"> <span class="keyword">where</span> 条件</span><br><span class="line"> <span class="keyword">order</span> <span class="keyword">by</span> 字段名 <span class="keyword">asc</span><span class="operator">/</span><span class="keyword">desc</span></span><br><span class="line"> limit 开始记录号、条数</span><br><span class="line"></span><br><span class="line"> (<span class="number">1</span>)查询 名字以“魏”开头的学生信息</span><br><span class="line"> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;魏%&#x27;</span></span><br><span class="line"> （<span class="number">2</span>）多条件查询 <span class="keyword">and</span> 或 <span class="keyword">or</span>（<span class="keyword">and</span>优先级比<span class="keyword">or</span>高,通过括号可以改变优先级）</span><br><span class="line"> <span class="keyword">where</span> 条件<span class="number">1</span> <span class="keyword">or</span> 条件<span class="number">2</span> <span class="keyword">and</span> 条件<span class="number">3</span></span><br><span class="line"> （条件<span class="number">1</span>（条件<span class="number">2</span>、条件<span class="number">3</span>））</span><br><span class="line"> （<span class="number">3</span>）<span class="keyword">order</span> <span class="keyword">by</span> 排序 可以加多个字段来进行组合</span><br><span class="line"> eg：查询学生信息里面，按班级id顺序排，同一个班级里面，按照学号倒序排。</span><br><span class="line"> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student </span><br><span class="line"> <span class="operator">&lt;</span><span class="operator">!</span><span class="comment">-- order by classes_id asc,no asc --&gt;</span></span><br><span class="line"> <span class="keyword">order</span> <span class="keyword">by</span> classes_id <span class="keyword">no</span>,<span class="keyword">asc</span></span><br><span class="line"></span><br></pre></td></tr></table></figure></li>
</ul>
</li>
</ul>
<h3 id="二、数据的录入"><a href="#二、数据的录入" class="headerlink" title="二、数据的录入"></a>二、数据的录入</h3><h3 id="三、sql的使用（简单查询、复合查询、统计）"><a href="#三、sql的使用（简单查询、复合查询、统计）" class="headerlink" title="三、sql的使用（简单查询、复合查询、统计）"></a>三、sql的使用（简单查询、复合查询、统计）</h3><h3 id="四、视图view的使用"><a href="#四、视图view的使用" class="headerlink" title="四、视图view的使用"></a>四、视图view的使用</h3><h3 id="五、触发器trigger的使用"><a href="#五、触发器trigger的使用" class="headerlink" title="五、触发器trigger的使用"></a>五、触发器trigger的使用</h3><h3 id="六、存储过程的使用"><a href="#六、存储过程的使用" class="headerlink" title="六、存储过程的使用"></a>六、存储过程的使用</h3><h2 id="day3-多表查询"><a href="#day3-多表查询" class="headerlink" title="day3:多表查询"></a>day3:多表查询</h2><hr>
<blockquote>
<p>多表查询：联合两个以上的表进行查询</p>
</blockquote>
<ul>
<li>（1）确定要查询的信息，在哪些表里面存在</li>
<li>（2）确定表之间的对应关系</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 表<span class="number">1.</span>字段，表<span class="number">1.</span>字段名...表<span class="number">2.</span>字段名...</span><br><span class="line"><span class="keyword">from</span> 表<span class="number">1</span>，表<span class="number">2</span>，表<span class="number">3.</span>..</span><br><span class="line"><span class="keyword">where</span> 表<span class="number">1.</span>字段名 <span class="operator">=</span> 表<span class="number">2.</span>字段名</span><br><span class="line"></span><br><span class="line">eg:</span><br><span class="line">(<span class="number">1</span>)查询教师信息，显示所在学院名称</span><br><span class="line"><span class="keyword">select</span> teacher.<span class="operator">*</span>,college.name <span class="keyword">as</span> college_name</span><br><span class="line"><span class="keyword">from</span> teacher,college</span><br><span class="line"><span class="keyword">where</span> teacher.college_id <span class="operator">=</span> college.id</span><br><span class="line"></span><br><span class="line">别名：对要查询的表名，在<span class="keyword">from</span>的时候，使用别名</span><br><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>,t2.name <span class="keyword">as</span> college_name</span><br><span class="line"><span class="keyword">from</span> teacher <span class="keyword">as</span> t1,college <span class="keyword">as</span> t2</span><br><span class="line"><span class="keyword">where</span> t1.college_id <span class="operator">=</span> t2.id</span><br><span class="line"></span><br><span class="line">(<span class="number">2</span>)带条件的多表查询</span><br><span class="line">    <span class="comment">---查询名字中含“魏”的教师信息，显示所在学院名称</span></span><br><span class="line"><span class="keyword">select</span> teacher.<span class="operator">*</span>,college.name <span class="keyword">as</span> college_name</span><br><span class="line"><span class="keyword">from</span> teacher,college</span><br><span class="line"><span class="keyword">where</span> teacher.college_id <span class="operator">=</span> college.id <span class="keyword">and</span> teacher.name <span class="keyword">like</span> <span class="string">&#x27;%魏%&#x27;</span></span><br><span class="line"></span><br><span class="line">假设有<span class="number">3000</span>条教师记录，<span class="number">22</span>条学院记录</span><br><span class="line">运算次数：<span class="number">3000</span><span class="operator">*</span><span class="number">22</span><span class="operator">+</span><span class="number">3000</span></span><br><span class="line"></span><br><span class="line">优化：先按条件进行筛选，再进行表的联合</span><br><span class="line"><span class="keyword">select</span> teacher.<span class="operator">*</span>,college.name <span class="keyword">as</span> college_name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> teacher <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;%魏%&#x27;</span>) <span class="keyword">as</span> t1,college <span class="keyword">as</span> t2</span><br><span class="line"><span class="keyword">where</span> t1.college_id <span class="operator">=</span> t2.id</span><br><span class="line"></span><br><span class="line">假设有<span class="number">3000</span>条教师记录，<span class="number">10</span> 条含‘魏’的教师记录，<span class="number">22</span>条学院记录</span><br><span class="line">运算次数：<span class="number">3000</span><span class="operator">+</span><span class="number">10</span><span class="operator">*</span><span class="number">22</span></span><br><span class="line"></span><br><span class="line">(<span class="number">3</span>)<span class="number">3</span>个以上表的查询</span><br><span class="line">    <span class="comment">---列出班级信息，显示所在学院名称、专业名称、班主任教师名称</span></span><br><span class="line"><span class="keyword">select</span> classes.<span class="operator">*</span>,college.name <span class="keyword">as</span> college_name,major.name <span class="keyword">as</span> major_name,</span><br><span class="line">teacher.name <span class="keyword">as</span> head_teacher_name</span><br><span class="line"><span class="keyword">from</span> classes,college,major,teacher</span><br><span class="line"><span class="keyword">where</span> classes.college_id <span class="operator">=</span> college.id</span><br><span class="line"><span class="keyword">and</span> classes.major_id <span class="operator">=</span> major.id</span><br><span class="line"><span class="keyword">and</span> classes.head_teacher_id <span class="operator">=</span> teacher.id</span><br><span class="line"></span><br><span class="line">    <span class="comment">---列出学生信息，显示班主任教师名称</span></span><br><span class="line"><span class="keyword">select</span> student.<span class="operator">*</span>,teacher.name <span class="keyword">as</span> head_teacher_name</span><br><span class="line"><span class="keyword">from</span> student,teacher,classes</span><br><span class="line"><span class="keyword">where</span> student.classer_id <span class="operator">=</span> classes.id</span><br><span class="line"><span class="keyword">and</span> classes.head_teacher_id <span class="operator">=</span> teacher.id</span><br><span class="line"></span><br><span class="line">    <span class="comment">---查询名字中姓张的学生信息，班主任教师名称</span></span><br><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>,teacher.name <span class="keyword">as</span> head_teacher_name</span><br><span class="line"><span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;张%&#x27;</span>) <span class="keyword">as</span> t1,teacher,classes</span><br><span class="line"><span class="keyword">where</span> t1.classer_id <span class="operator">=</span> classes.id</span><br><span class="line"><span class="keyword">and</span> classes.head_teacher_id <span class="operator">=</span> teacher.id</span><br></pre></td></tr></table></figure>
<p>补充：假设两个表A(m条记录)，B(n条记录)进行联合查询过程是将A中的每条记录依次和B中的每条记录进行运算（笛卡尔乘积），时间消耗是m*n。</p>
<h2 id="day3：统计"><a href="#day3：统计" class="headerlink" title="day3：统计"></a>day3：统计</h2><hr>
<ul>
<li>(1)统计记录的数量<code>count</code></li>
<li>(2)对某一列的数据进行汇总<code>sum、avg、max、min</code></li>
<li>(3)按照指定的列，进行累加<code>group by</code></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1</span>、统计所有教师的数量</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="comment">---实际中一般不用count(*)</span></span><br><span class="line"><span class="keyword">from</span> teacher</span><br><span class="line"></span><br><span class="line"><span class="number">2</span>、统计每个学院教师的数量</span><br><span class="line"><span class="keyword">select</span> college_id.<span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line"><span class="keyword">from</span> teacher</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line"></span><br><span class="line"><span class="number">3</span>、统计每个学院教师的数量，只显示数量超过<span class="number">100</span>的数据，并且按照数量从高到低排列</span><br><span class="line"><span class="keyword">select</span> college_id,<span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line"><span class="keyword">from</span> teacher</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="operator">&gt;=</span> <span class="number">100</span>   <span class="comment">---having对统计之后数据的筛选</span></span><br><span class="line"><span class="keyword">having</span> <span class="keyword">by</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">desc</span></span><br><span class="line"></span><br><span class="line"><span class="number">4</span>、统计每个学院教师的数量，在结果中显示学院的名称</span><br><span class="line"><span class="comment">--错误写法：先联合再统计</span></span><br><span class="line"><span class="keyword">select</span> college_name,<span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">(</span><br><span class="line"><span class="keyword">select</span> teacher.<span class="operator">*</span>,college.name <span class="keyword">as</span> college_name</span><br><span class="line"><span class="keyword">from</span> teacher,college</span><br><span class="line"><span class="keyword">where</span> teacher.college_id <span class="operator">=</span> college.id</span><br><span class="line">) <span class="keyword">as</span> t1</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> college_name</span><br><span class="line"></span><br><span class="line"><span class="comment">--正确写法：先统计再联合</span></span><br><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>,college.name</span><br><span class="line"><span class="keyword">from</span> (</span><br><span class="line">  <span class="keyword">select</span> college_id,<span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line">  <span class="keyword">from</span> teacher</span><br><span class="line">  <span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line">) <span class="keyword">as</span> t1,college</span><br><span class="line"><span class="keyword">where</span> t1.college_id <span class="operator">=</span> college.id</span><br><span class="line"></span><br><span class="line"><span class="number">5</span>、统计每个学院的学生人数，结果中显示学院的名称、学生数量</span><br><span class="line"><span class="comment">--- （1）联合student，classes得到每个学生所在的college_id</span></span><br><span class="line"><span class="keyword">select</span> student.<span class="operator">*</span>,college_id</span><br><span class="line"><span class="keyword">from</span> student,classes</span><br><span class="line"><span class="keyword">where</span> student.classes_id <span class="operator">=</span> classes.id</span><br><span class="line"></span><br><span class="line"><span class="comment">---  (2) 再对联合后的结果，按照college_id进行统计</span></span><br><span class="line"><span class="keyword">select</span> college_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line"><span class="keyword">from</span> (</span><br><span class="line">  <span class="keyword">select</span> student.<span class="operator">*</span>,college_id</span><br><span class="line">  <span class="keyword">from</span> student,classes</span><br><span class="line">  <span class="keyword">where</span> student.classes_id <span class="operator">=</span> classes.id</span><br><span class="line">) <span class="keyword">as</span> t1</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line"></span><br><span class="line"><span class="comment">---  (3) 最后将统计的结果联合college表，得到college_name</span></span><br><span class="line"><span class="keyword">select</span> t2.<span class="operator">*</span>,college.name</span><br><span class="line"><span class="keyword">from</span> (</span><br><span class="line">    <span class="keyword">select</span> college_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line">    <span class="keyword">from</span> (</span><br><span class="line">      <span class="keyword">select</span> student.<span class="operator">*</span>,college_id</span><br><span class="line">      <span class="keyword">from</span> student,classes</span><br><span class="line">      <span class="keyword">where</span> student.classer_id <span class="operator">=</span> classes.id</span><br><span class="line">    ) <span class="keyword">as</span> t1</span><br><span class="line">    <span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line">) <span class="keyword">as</span> t2,college</span><br><span class="line"><span class="keyword">where</span> t2.college_id <span class="operator">=</span> college.id</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="day4"><a href="#day4" class="headerlink" title="day4:"></a>day4:</h2><hr>
<h3 id="1、limit和order-by-的使用"><a href="#1、limit和order-by-的使用" class="headerlink" title="1、limit和order by 的使用"></a>1、limit和order by 的使用</h3><ul>
<li><p>limit：根据给定的行号，从查询结果里面，取给定数量的记录。limit开始行号（从0开始编号），数量。</p>
</li>
<li><p>order by 字段名[asc/desc]:将询的结果数据，按照给定字段名的值进行排序。</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br></pre></td><td class="code"><pre><span class="line">eg: <span class="number">1</span>、查询学院人数中排名前三的信息、显示学院名称、学生人数</span><br><span class="line">    <span class="comment">---（1）联合学生信息表、班级信息表，得到每个学生所在学院id</span></span><br><span class="line">    <span class="comment">---（2）按照学院id，进行学生数量汇总</span></span><br><span class="line">    <span class="comment">---（3）对汇总后的数据排序，取前三条记录</span></span><br><span class="line">    <span class="comment">---（4）联合学院信息表，得到学院名称</span></span><br><span class="line"></span><br><span class="line">    <span class="comment">---注意：先排序取前三，再联合（3）（4）的顺序不能颠倒，颠倒后效率会差很多</span></span><br><span class="line">    <span class="keyword">select</span> t2.<span class="operator">*</span>, college.name <span class="keyword">as</span> college_name</span><br><span class="line">    <span class="keyword">from</span></span><br><span class="line">    (</span><br><span class="line">    #按照学院id，进行汇总 ，排序，取前三</span><br><span class="line">    <span class="keyword">select</span> college_id , <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line">    <span class="keyword">from</span></span><br><span class="line">    #联合学生与班级，得到学院id  </span><br><span class="line">    (    <span class="keyword">select</span>  student.<span class="operator">*</span>, college_id</span><br><span class="line">              <span class="keyword">from</span> student, classes</span><br><span class="line">            <span class="keyword">where</span>  student.class_id   <span class="operator">=</span> classes.id</span><br><span class="line">          )<span class="keyword">as</span>  t1</span><br><span class="line">    <span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line">    <span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">desc</span>  #排序，按照学生人数倒序排列</span><br><span class="line">    limit <span class="number">0</span>, <span class="number">3</span>    #取前<span class="number">3</span></span><br><span class="line">    )<span class="keyword">as</span> t2, college</span><br><span class="line">    <span class="keyword">where</span>  t2.college_id <span class="operator">=</span> college.id</span><br><span class="line"></span><br><span class="line"><span class="comment">---随堂练习：</span></span><br><span class="line"><span class="comment">---（1）查询学生人数排名前三的专业，显示专业名称、学生人数</span></span><br><span class="line"><span class="keyword">select</span> t2.<span class="operator">*</span>,major.name <span class="keyword">as</span> major_name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span> major_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span>  student.<span class="operator">*</span>, major_id</span><br><span class="line"><span class="keyword">from</span> student, classes</span><br><span class="line"><span class="keyword">where</span>  student.classer_id   <span class="operator">=</span> classes.id)<span class="keyword">as</span> t1</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> major_id</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">desc</span></span><br><span class="line">limit <span class="number">0</span>,<span class="number">3</span>) <span class="keyword">as</span> t2,major</span><br><span class="line"><span class="keyword">where</span> t2.major_id <span class="operator">=</span> major.id</span><br><span class="line"></span><br><span class="line"><span class="comment">---（2）查询学生人数排名前十的专业，显示专业名称、学生人数、所在学院名称</span></span><br><span class="line"><span class="keyword">select</span> t2.<span class="operator">*</span>,major.name <span class="keyword">as</span> major_name,college.name <span class="keyword">as</span> college_name </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span> major_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span>  student.<span class="operator">*</span>, major_id,college_id</span><br><span class="line"><span class="keyword">from</span> student, classes</span><br><span class="line"><span class="keyword">where</span>  student.classer_id <span class="operator">=</span> classes.id)<span class="keyword">as</span> t1</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> major_id</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">desc</span></span><br><span class="line">limit <span class="number">0</span>,<span class="number">10</span>) <span class="keyword">as</span> t2,major,college</span><br><span class="line"><span class="keyword">where</span> t2.major_id <span class="operator">=</span> major.id <span class="keyword">and</span> major.college_id <span class="operator">=</span> college.id</span><br><span class="line"></span><br><span class="line"><span class="comment">---（3）查询学院里面，学生人数排名在前十的专业数量，显示学院名称，学生人数排名在前十的专业数量</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="2、视图view的使用"><a href="#2、视图view的使用" class="headerlink" title="2、视图view的使用"></a>2、视图view的使用</h3><ul>
<li>将多表联合查询的逻辑关系作为一个独立的对象，在查询中直接通过视图来进行访问</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">---(1)视图view的创建：一次创建后可以反复使用</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> 视图名</span><br><span class="line"><span class="keyword">as</span> 联合查询语句</span><br><span class="line"></span><br><span class="line"><span class="comment">---(2)视图的使用，在查询sql里面，直接用视图名</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 视图名</span><br><span class="line"></span><br><span class="line"><span class="comment">---eg:创建视图v_student：联合学生表与班级表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> v_student </span><br><span class="line"><span class="keyword">as</span> </span><br><span class="line"><span class="keyword">select</span>  student.<span class="operator">*</span>, college_id</span><br><span class="line"><span class="keyword">from</span> student, classes</span><br><span class="line"><span class="keyword">where</span>  student.classer_id   <span class="operator">=</span> classes.id;</span><br><span class="line"><span class="comment">---eg:视图的使用1</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> v_student;</span><br><span class="line"><span class="comment">---eg:视图的使用2</span></span><br><span class="line"><span class="keyword">select</span> college_id , <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line"><span class="keyword">from</span> v_student</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="day5"><a href="#day5" class="headerlink" title="day5"></a>day5</h2><hr>
<h3 id="创建成绩相关的表"><a href="#创建成绩相关的表" class="headerlink" title="创建成绩相关的表"></a>创建成绩相关的表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">(<span class="number">1</span>)课程信息表course:    </span><br><span class="line">        id,<span class="keyword">no</span>,name,credit,college_id</span><br><span class="line"></span><br><span class="line">(<span class="number">2</span>)学期信息表semester</span><br><span class="line">        id,name</span><br><span class="line">        </span><br><span class="line">(<span class="number">3</span>)班级课程安排信息表 course_classes</span><br><span class="line">      id, semester_id,  </span><br><span class="line">     class_id,course_id,teacher_id</span><br><span class="line"></span><br><span class="line">(<span class="number">4</span>)学生课程成绩信息表 score</span><br><span class="line">     id,student_id, semester_id,course_id,teacher_id,score</span><br></pre></td></tr></table></figure>
<p>统计函数：max，min，avg，sum</p>
<h3 id="实现对某门课程-某个班级期末成绩的分析"><a href="#实现对某门课程-某个班级期末成绩的分析" class="headerlink" title="实现对某门课程 某个班级期末成绩的分析"></a>实现对<code>某门课程</code> <code>某个班级</code>期末成绩的分析</h3><p>(1)最高分、最低分、平均分</p>
<p>(2)及格率（分数&gt;60的人数）/班级人数</p>
<p>(3)各分数段的人数</p>
<pre><code>[90-100]  xxx
[80-90)   xxx
[70-80)   xxx
[60-70)   xx
[0-60]    xx
</code></pre>
<p>思路：</p>
<p>（1）从成绩表score里面筛选中指定课程的记录 —t1</p>
<p>（2）从学生表里面筛选中指定班级的学生记录 —t2</p>
<p>（3）联合t1,t2得到指定班级指定课程的成绩记录 —t3</p>
<p>（4）对t3的结果进行max，min，avg的统计</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(score), <span class="built_in">min</span>(score), <span class="built_in">avg</span>(score)</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">  (</span><br><span class="line">   <span class="keyword">select</span>  score</span><br><span class="line">   <span class="keyword">from</span> </span><br><span class="line">      ( <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score  <span class="keyword">where</span>  course_id <span class="operator">=</span> xxx )<span class="keyword">as</span> t1,</span><br><span class="line">      (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student  <span class="keyword">where</span>  class_id  <span class="operator">=</span> xxx ) <span class="keyword">as</span> t2</span><br><span class="line">   <span class="keyword">where</span>  t1.student_id  <span class="operator">=</span> t2.id</span><br><span class="line">  )<span class="keyword">as</span> t3</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p> (5)班级学生人数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num_student </span><br><span class="line"><span class="keyword">from</span> student wher class_id <span class="operator">=</span> xx</span><br></pre></td></tr></table></figure>

<p> (6) 分数及格人数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num_pass</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">    ( <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score  <span class="keyword">where</span>  course_id <span class="operator">=</span> xxx <span class="keyword">and</span> score<span class="operator">&gt;=</span><span class="number">60</span>  )<span class="keyword">as</span> t1,</span><br><span class="line">    (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student  <span class="keyword">where</span>  class_id  <span class="operator">=</span> xxx ) <span class="keyword">as</span> t2</span><br><span class="line"><span class="keyword">where</span>  t1.student_id  <span class="operator">=</span> t2.id</span><br></pre></td></tr></table></figure>
<p>（7）分数大于90的人数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"> <span class="comment">---[90--100]的人数</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num90</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">  ( <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score  <span class="keyword">where</span>  course_id <span class="operator">=</span> xxx <span class="keyword">and</span> score<span class="operator">&gt;=</span><span class="number">90</span> <span class="keyword">and</span> score<span class="operator">&lt;=</span><span class="number">100</span>  )<span class="keyword">as</span> t1,</span><br><span class="line">  (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student  <span class="keyword">where</span>  class_id  <span class="operator">=</span> xxx ) <span class="keyword">as</span> t2</span><br><span class="line"><span class="keyword">where</span>  t1.student_id  <span class="operator">=</span> t2.id</span><br></pre></td></tr></table></figure>

<hr>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">---having 对统计之后结果的筛选</span></span><br><span class="line">eg:   统计某个班，平均分<span class="operator">&gt;</span><span class="number">80</span>的学生信息</span><br><span class="line"></span><br><span class="line">(<span class="number">1</span>) 选出某个班所有学生的成绩记录</span><br><span class="line">(<span class="number">2</span>)按照学生进行成绩的汇总，求每个学生的平均分</span><br><span class="line">(<span class="number">3</span>)对汇总的结果  筛选中<span class="operator">&gt;</span><span class="number">80</span>的记录</span><br><span class="line">(<span class="number">4</span>)对学生表进行联合 获得学生姓名等信息</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  student_id,<span class="built_in">avg</span>(score) </span><br><span class="line"><span class="keyword">from</span>  </span><br><span class="line">(</span><br><span class="line">  <span class="keyword">select</span>  student_id, score</span><br><span class="line">  <span class="keyword">from</span> score, (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> class_id <span class="operator">=</span> xx ) <span class="keyword">as</span> t1</span><br><span class="line">  <span class="keyword">where</span> score.student_id  <span class="operator">=</span> t1.id </span><br><span class="line">)<span class="keyword">as</span> t2    </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">avg</span>(score）<span class="operator">&gt;</span><span class="number">80</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>


<hr>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">---作业</span></span><br><span class="line"><span class="comment">---统计某个班 某门课 成绩大于该班班级该门课平均分的学生信息，显示学生姓名，分数</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_name,score</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(</span><br><span class="line">  <span class="keyword">select</span> coures_id,score</span><br><span class="line">  <span class="keyword">from</span> </span><br><span class="line">  (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> class_id <span class="operator">=</span> xx) <span class="keyword">as</span> c1,</span><br><span class="line">  (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score  <span class="keyword">where</span>  course_id <span class="operator">=</span> xxx) <span class="keyword">as</span> c2</span><br><span class="line">  <span class="keyword">where</span> c2.student_id <span class="operator">=</span> c1.id</span><br><span class="line">  <span class="keyword">group</span> <span class="keyword">by</span> coures_id</span><br><span class="line">  <span class="keyword">having</span> <span class="built_in">avg</span>(score)</span><br><span class="line">) <span class="keyword">as</span> score_avg,</span><br><span class="line">(</span><br><span class="line">  <span class="keyword">select</span> student_id,score</span><br><span class="line">  <span class="keyword">from</span> </span><br><span class="line">  (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> class_id <span class="operator">=</span> xx) <span class="keyword">as</span> s1,</span><br><span class="line">  (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score  <span class="keyword">where</span>  course_id <span class="operator">=</span> xxx) <span class="keyword">as</span> s2</span><br><span class="line">  <span class="keyword">where</span> s2.student_id <span class="operator">=</span> s1.id</span><br><span class="line">) <span class="keyword">as</span> all_score,student</span><br><span class="line"><span class="keyword">where</span> all_score.student_id <span class="operator">=</span> student.id</span><br><span class="line"><span class="keyword">and</span> all_score.score <span class="operator">&gt;</span> score_avg.score</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="day6"><a href="#day6" class="headerlink" title="day6"></a>day6</h2><hr>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="comment">---知识点：根据字段的内容，显示不同的信息 case when</span></span><br><span class="line"><span class="comment">---基本语法(1)</span></span><br><span class="line"><span class="keyword">case</span> 某个字段</span><br><span class="line">  <span class="keyword">when</span> 情况<span class="number">1</span> <span class="keyword">then</span> xxx</span><br><span class="line">  <span class="keyword">when</span> 情况<span class="number">2</span> <span class="keyword">then</span> xxx</span><br><span class="line">  <span class="keyword">else</span></span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"><span class="comment">---基本语法(2)</span></span><br><span class="line"><span class="keyword">case</span></span><br><span class="line">  <span class="keyword">when</span> 针对某个字段的条件表达式<span class="number">1</span> <span class="keyword">when</span></span><br><span class="line">  <span class="keyword">when</span> 针对某个字段的条件表达式<span class="number">2</span> <span class="keyword">when</span></span><br><span class="line"><span class="keyword">else</span></span><br><span class="line">  xxx</span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---1、查询学生成绩信息，根据分数的范围，显示不同的等级</span></span><br><span class="line"><span class="comment">--- [0,60)不及格   [60,80)及格   [80,90)良好   [90,100]优秀</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span>,<span class="keyword">case</span></span><br><span class="line">            <span class="keyword">when</span> score<span class="operator">&lt;</span><span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;不及格&#x27;</span></span><br><span class="line">            <span class="keyword">when</span> score<span class="operator">&gt;=</span><span class="number">60</span>  <span class="keyword">and</span> score<span class="operator">&lt;</span><span class="number">80</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span></span><br><span class="line">            <span class="keyword">when</span> score<span class="operator">&gt;=</span><span class="number">80</span>  <span class="keyword">and</span> score<span class="operator">&lt;</span><span class="number">90</span> <span class="keyword">then</span> <span class="string">&#x27;良好&#x27;</span></span><br><span class="line">            <span class="keyword">else</span> <span class="string">&#x27;优秀&#x27;</span></span><br><span class="line">         <span class="keyword">end</span>  <span class="keyword">as</span>  score_level</span><br><span class="line"><span class="keyword">from</span> score</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---2、查询某门课分数超过平均分的学生成绩</span></span><br><span class="line"><span class="comment">---（1）获得学生成绩</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score</span><br><span class="line"><span class="keyword">where</span> course_id <span class="operator">=</span> xxx <span class="keyword">and</span> score <span class="operator">&gt;</span> 课程成绩的平均分</span><br><span class="line"></span><br><span class="line"><span class="comment">---（2）获得课程平均分</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(score) <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="operator">=</span> xxx</span><br><span class="line"></span><br><span class="line"><span class="comment">---合并</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> score </span><br><span class="line"><span class="keyword">where</span> course_id <span class="operator">=</span><span class="number">1</span>   </span><br><span class="line"><span class="keyword">and</span> score <span class="operator">&gt;</span> ( </span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(score) <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="operator">=</span> <span class="number">1</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">---拓展：查询在所有课程里面，成绩超过课程平均分的成绩信息</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---3、查询每个学生成绩超过课程平均分的课程数量，显示学生姓名、超过课程平均分的课程数量</span></span><br><span class="line"><span class="comment">---(1)求出成绩表里面，每门课的平均分   course_id,   avg_score</span></span><br><span class="line"><span class="keyword">select</span>  course_id , <span class="built_in">avg</span>( score ) <span class="keyword">as</span> avg_score</span><br><span class="line"><span class="keyword">from</span> score </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> course_id</span><br><span class="line"><span class="comment">---（2）将成绩表(student_id,course_id,score)与 (1) 进行联合,并且取出成绩 大于 平均分的记录</span></span><br><span class="line"><span class="keyword">select</span>  score.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> score,  ( 步骤<span class="number">1</span> ) <span class="keyword">as</span> t1</span><br><span class="line"><span class="keyword">where</span>   score.couse_id  <span class="operator">=</span> t1.course_id</span><br><span class="line"><span class="keyword">and</span>  score.score <span class="operator">&gt;</span> t1.avg_score</span><br><span class="line"><span class="comment">---(3) 将步骤2的结果 ，根据学生id  来汇总数量</span></span><br><span class="line"><span class="keyword">select</span>  student_id, <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num_couse</span><br><span class="line"><span class="keyword">from</span>  （ 步骤<span class="number">2</span> ）<span class="keyword">as</span> t3</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---4、查询所有课程都超过课程平均分的学生信息</span></span><br><span class="line"><span class="comment">---（1）从成绩记录里面，得到每个学生的课程数量</span></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num_total</span><br><span class="line"><span class="keyword">from</span> score</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line"><span class="comment">---（2）联合（1）与第三题结果，取出num_coursenum_course = num_total的记录</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line"><span class="keyword">from</span> (第一步) <span class="keyword">as</span> t_total,(第三题) <span class="keyword">as</span> t_avg</span><br><span class="line"><span class="keyword">where</span> t_total.student_id <span class="operator">=</span> tv_avg.student_id</span><br><span class="line"><span class="keyword">and</span> t_avg.num_course <span class="operator">=</span> t_total.num_total</span><br><span class="line"></span><br></pre></td></tr></table></figure>


<h2 id="day7"><a href="#day7" class="headerlink" title="day7"></a>day7</h2><hr>
<p> 常用的一些技巧<br> <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br></pre></td><td class="code"><pre><span class="line"> <span class="comment">---1、汇总后的结果，求最大、最小、前n个</span></span><br><span class="line"> eg:查询某个班，平均分最高的学生信息</span><br><span class="line"> <span class="keyword">select</span> student_id,<span class="built_in">avg</span>(score) <span class="keyword">as</span> avg_score</span><br><span class="line"> <span class="keyword">from</span> score</span><br><span class="line"> <span class="keyword">where</span> student_id <span class="keyword">in</span> (<span class="keyword">select</span> id <span class="keyword">from</span> student <span class="keyword">where</span> class_id <span class="operator">=</span> xx)</span><br><span class="line"> <span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line"> <span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">avg</span>(score) <span class="keyword">desc</span></span><br><span class="line"> limit <span class="number">0</span>,<span class="number">1</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"> <span class="comment">--2. 查询某个班的学生平均成绩，从高到低排列，且输出结果里面，带序号</span></span><br><span class="line">    <span class="comment">--  1  张山   98</span></span><br><span class="line">    <span class="comment">--  2  李四   97</span></span><br><span class="line">    <span class="comment">--  3  王二   89</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>,<span class="variable">@i</span> :<span class="operator">=</span> <span class="variable">@i</span><span class="operator">+</span><span class="number">1</span> <span class="keyword">as</span> idx</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">( </span><br><span class="line">  <span class="keyword">select</span> student_id, <span class="built_in">avg</span>(score)  <span class="keyword">as</span> avg_score</span><br><span class="line">  <span class="keyword">from</span> score</span><br><span class="line">  <span class="keyword">where</span>  student_id <span class="keyword">in</span> (<span class="keyword">select</span> id <span class="keyword">from</span> student <span class="keyword">where</span> class_id  <span class="operator">=</span> xx)</span><br><span class="line">  <span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line">  <span class="keyword">order</span> <span class="keyword">by</span>  <span class="built_in">avg</span>(score) <span class="keyword">desc</span></span><br><span class="line">) <span class="keyword">as</span> t1,( <span class="keyword">select</span> <span class="variable">@i</span> :<span class="operator">=</span> <span class="number">0</span> ) <span class="keyword">as</span> t2</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---3、随机：从给定数据里面，随机获得n条记录</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- order by rand()</span></span><br><span class="line"><span class="comment">-- limit 0,n</span></span><br><span class="line"></span><br><span class="line">eg:随机挑选<span class="number">3</span>个学生</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> rand()</span><br><span class="line">limit <span class="number">0</span>,<span class="number">3</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---4、多重排序</span></span><br><span class="line"><span class="comment">--- （1）统计每个班平均成绩，按照班级名称升序排列，按照成绩降序排列，输出样例</span></span><br><span class="line"><span class="comment">--- A1951 张1 98 </span></span><br><span class="line"><span class="comment">--- A1951 张2 90 </span></span><br><span class="line"><span class="comment">--- A1951 张3 80 </span></span><br><span class="line"><span class="comment">--- A1952 李1 96 </span></span><br><span class="line"><span class="comment">--- A1952 李2 90 </span></span><br><span class="line"><span class="comment">--- A1952 李3 88 </span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span></span><br><span class="line">(</span><br><span class="line">  <span class="keyword">select</span> avg_score,student.name <span class="keyword">as</span> student_name,classes.name  <span class="keyword">as</span> class_name</span><br><span class="line">  <span class="keyword">from</span></span><br><span class="line">  (</span><br><span class="line">    <span class="keyword">select</span> student_id,<span class="built_in">avg</span>(score) <span class="keyword">as</span> avg_score</span><br><span class="line">    <span class="keyword">from</span> score</span><br><span class="line">    <span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line">  ) <span class="keyword">as</span> t1,student,classes</span><br><span class="line">  <span class="keyword">where</span> t1.student_id <span class="operator">=</span> student.id</span><br><span class="line">  <span class="keyword">and</span> student.class_id <span class="operator">=</span> classes.id</span><br><span class="line">) <span class="keyword">as</span> t2</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> class_name <span class="keyword">asc</span> ,score <span class="keyword">desc</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">--- 作业：（2）统计每个班平均成绩排名前三的学生，按照班级名称升序排列，按照成绩降序排列，输出样例</span></span><br><span class="line"><span class="comment">--- A1951 张1 98 1</span></span><br><span class="line"><span class="comment">--- A1951 张2 90 2</span></span><br><span class="line"><span class="comment">--- A1951 张3 80 3</span></span><br><span class="line"><span class="comment">--- A1952 李1 96 1</span></span><br><span class="line"><span class="comment">--- A1952 李2 90 2</span></span><br><span class="line"><span class="comment">--- A1952 李3 88 3</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span></span><br><span class="line">(</span><br><span class="line">  <span class="keyword">select</span> <span class="operator">*</span>,<span class="built_in">row_number</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> t2.class_name <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) <span class="keyword">as</span> ranking</span><br><span class="line">  <span class="keyword">from</span></span><br><span class="line">  (</span><br><span class="line">    <span class="keyword">select</span> avg_score,student.name <span class="keyword">as</span> student_name,classes.name  <span class="keyword">as</span> class_name</span><br><span class="line">    <span class="keyword">from</span></span><br><span class="line">    (</span><br><span class="line">      <span class="keyword">select</span> student_id,<span class="built_in">avg</span>(score) <span class="keyword">as</span> avg_score</span><br><span class="line">      <span class="keyword">from</span> score</span><br><span class="line">      <span class="keyword">group</span> <span class="keyword">by</span> student_id</span><br><span class="line">    ) <span class="keyword">as</span> t1,student,classes</span><br><span class="line">    <span class="keyword">where</span> t1.student_id <span class="operator">=</span> student.id</span><br><span class="line">    <span class="keyword">and</span> student.class_id <span class="operator">=</span> classes_id</span><br><span class="line">  ) <span class="keyword">as</span> t2</span><br><span class="line">  <span class="keyword">order</span> <span class="keyword">by</span> class_name <span class="keyword">asc</span> ,score <span class="keyword">desc</span></span><br><span class="line">) <span class="keyword">as</span> t3</span><br><span class="line"><span class="keyword">where</span> t3.ranking <span class="operator">&lt;=</span> <span class="number">3</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> class_name <span class="keyword">asc</span> ,score <span class="keyword">desc</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---5、联合</span></span><br><span class="line"></span><br><span class="line"><span class="comment">---内联合   t1  ,  t2    从集合t1中，取 第i条件，代入到联合条件中，如果满足条件，则被选中，否则，被舍去</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">---外联合 outer join    t1  ,  t2    从集合t1中，取 第i条件，代入到联合条件中不满足条件的，仍然保留在结果中，t2对应的字段内容为null</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--eg:  查询所有学院的教师数量，保留没有教师记录的学院</span></span><br><span class="line"><span class="keyword">select</span> college.<span class="operator">*</span>, ifnull(t1.num,<span class="number">0</span>) <span class="keyword">as</span> num</span><br><span class="line"><span class="keyword">from</span>  college  <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> </span><br><span class="line">(  <span class="keyword">select</span>  college_id,<span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">as</span> num</span><br><span class="line">   <span class="keyword">from</span> teacher</span><br><span class="line">   <span class="keyword">group</span> <span class="keyword">by</span> college_id</span><br><span class="line">) <span class="keyword">as</span> t1   <span class="keyword">on</span>   college.id  <span class="operator">=</span> t1.college_id</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">使用ifnull(字段名,给定值):将为<span class="keyword">null</span>字段的内容设置为给定值</span><br></pre></td></tr></table></figure></p>
</div><div id="reward-container"><span class="hty-icon-button button-glow" id="reward-button" title="Donate" onclick="var qr = document.getElementById(&quot;qr&quot;); qr.style.display = (qr.style.display === &quot;none&quot;) ? &quot;block&quot; : &quot;none&quot;;"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-hand-coin-line"></use></svg></span><div id="reward-comment">I want you.</div><div id="qr" style="display:none;"><div style="display:inline-block"></div><div style="display:inline-block"></div><div style="display:inline-block"></div></div></div><ul class="post-copyright"><li class="post-copyright-author"><strong>Post author: </strong>zzl</li><li class="post-copyright-link"><strong>Post link: </strong><a href="http://zilongzeng.gitee.io/web/2022/04/28/sql%E8%AF%AD%E8%A8%80/" title="sql语言">http://zilongzeng.gitee.io/web/2022/04/28/sql%E8%AF%AD%E8%A8%80/</a></li><li class="post-copyright-license"><strong>Copyright Notice: </strong>All articles in this blog are licensed under <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" target="_blank" rel="noopener" title="CC BY-NC-SA 4.0 "><svg class="icon"><use xlink:href="#icon-creative-commons-line"></use></svg><svg class="icon"><use xlink:href="#icon-creative-commons-by-line"></use></svg><svg class="icon"><use xlink:href="#icon-creative-commons-nc-line"></use></svg><svg class="icon"><use xlink:href="#icon-creative-commons-sa-line"></use></svg></a> unless otherwise stated.</li></ul></section></article><div class="post-nav"><div class="post-nav-item"><a class="post-nav-prev" href="/web/2022/04/28/Spring%E5%AD%A6%E4%B9%A0/" rel="prev" title="Spring学习"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-arrow-left-s-line"></use></svg><span class="post-nav-text">Spring学习</span></a></div><div class="post-nav-item"><a class="post-nav-next" href="/web/2022/04/28/jdbc/" rel="next" title="jdbc"><span class="post-nav-text">jdbc</span><svg class="icon" aria-hidden="true"><use xlink:href="#icon-arrow-right-s-line"></use></svg></a></div></div></div><div class="hty-card" id="comment"><div class="comment-tooltip text-center"><span>要不要和我说些什么？</span><br></div></div></main><footer class="sidebar-translate" id="footer"><div class="copyright"><span>&copy; 2019 – 2022 </span><span class="with-love" id="animate"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-cloud-line"></use></svg></span><span class="author"> zzl</span></div><div class="powered"><span>Powered by <a href="https://hexo.io" target="_blank" rel="noopener">Hexo</a> v5.4.0</span><span class="footer-separator">|</span><span>Theme - <a rel="noopener" href="https://github.com/YunYouJun/hexo-theme-yun" target="_blank"><span>Yun</span></a> v1.6.2</span></div></footer><a class="hty-icon-button" id="back-to-top" aria-label="back-to-top" href="#"><svg class="icon" aria-hidden="true"><use xlink:href="#icon-arrow-up-s-line"></use></svg><svg class="progress-circle-container" viewBox="0 0 100 100"><circle class="progress-circle" id="progressCircle" cx="50" cy="50" r="48" fill="none" stroke="#0078E7" stroke-width="2" stroke-linecap="round"></circle></svg></a></div></body></html>